package com.ctb.dao;

import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import oracle.jdbc.OracleTypes;

import org.apache.log4j.Logger;

import utiles.AppProperties;
import utiles.ConexionPool;

import com.ctb.dto.ClienteDTO;
import com.ctb.vo.ClienteVO;



public class ClienteDAO {
	private static Logger log = Logger.getLogger(ClienteDAO.class);
	String pkgVar = (AppProperties.getProperty("aplicacion", "PACKAGE"));
	
		
	public  List<ClienteDTO> getCliente(String idCliente, String rut, String razon, String tipoCliente, String activoSN, String facturaSN, String formaPago) throws SQLException, IOException{
		
		Connection conn = ConexionPool.getConnectionPool();	
    	
		List<ClienteDTO> listRetorno = new ArrayList<ClienteDTO>();
		try {
			String query = "{ call "+pkgVar+".PKG_CON_CLIENTE.getCliente(?,?,?,?,?,?,?,?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.setString(1, idCliente);			
			stmt.setString(2, rut);
			stmt.setString(3, razon);
			stmt.setString(4, tipoCliente);
			stmt.setString(5, activoSN);
			stmt.setString(6, facturaSN);
			stmt.setString(7, formaPago);
			stmt.registerOutParameter(8, OracleTypes.CURSOR);
			stmt.execute();
			ResultSet rs = (ResultSet)stmt.getObject(8);
			while (rs.next()) {
				ClienteDTO clienteDTO = new ClienteDTO();
				clienteDTO.setIdCliente(rs.getString(1));
				clienteDTO.setIdTipoCliente(rs.getString(2));
				clienteDTO.setRut(rs.getString(3));
				clienteDTO.setDv(rs.getString(4));
				clienteDTO.setNomCorto(rs.getString(5));
				clienteDTO.setFacturaSN(rs.getString(6));
				clienteDTO.setActivoSN(rs.getString(7));
				clienteDTO.setIdComuna(rs.getString(8));
				clienteDTO.setRazonSocial(rs.getString(9));
				clienteDTO.setAreaEmpresa(rs.getString(10));
				clienteDTO.setContactoEmpresa(rs.getString(11));
				clienteDTO.setContactoTelefono(rs.getString(12));
				clienteDTO.setDireccion(rs.getString(13));
				clienteDTO.setEmpresaTelefono(rs.getString(14));
				clienteDTO.setCoberturaRSN(rs.getString(15));
				clienteDTO.setGiro(rs.getString(16));
				clienteDTO.setEmail(rs.getString(17));
				clienteDTO.setRutDV(rs.getString(18));
				clienteDTO.setTipoCliente(rs.getString(19));
				clienteDTO.setComuna(rs.getString(20));
				clienteDTO.setImgActivoSN(rs.getString(21));
				clienteDTO.setImgFacturaSN(rs.getString(22));
				clienteDTO.setCondCompra(rs.getString(23));
		    	listRetorno.add(clienteDTO);			    	
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  
			log.error("Error ClienteDAO.getCliente SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());
		} finally {			
			conn.close();
			conn = null;
		}
		return listRetorno;
	}
	public  List<ClienteDTO> setCliente(ClienteVO clienteVo) throws SQLException, IOException{

		Connection conn = ConexionPool.getConnectionPool();	

		List<ClienteDTO> listRetorno = new ArrayList<ClienteDTO>();
		try {
			String query = "{ call "+pkgVar+".PKG_CON_CLIENTE.setCliente(?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.setString(1, clienteVo.getIdCliente());		
			stmt.setString(2, clienteVo.getTipoCliente());	
			stmt.setString(3, clienteVo.getRut());	
			stmt.setString(4, clienteVo.getRutDV());	
			stmt.setString(5, clienteVo.getDescripcion());	
			stmt.setString(6, clienteVo.getFacturaSN());	
			stmt.setString(7, clienteVo.getActivoSN());
			stmt.setString(8, clienteVo.getComuna());
			stmt.setString(9, clienteVo.getRazonSocial());	
			stmt.setString(10, clienteVo.getAreaEmpresa());	
			stmt.setString(11, clienteVo.getContactoEmpresa());	
			stmt.setString(12, clienteVo.getContactoTelefono());
			stmt.setString(13, clienteVo.getDireccion());
			stmt.setString(14, clienteVo.getEmpresaTelefono());	
			stmt.setString(15, clienteVo.getCoberturaRSN());
			stmt.setString(16, clienteVo.getGiro());
			stmt.setString(17, clienteVo.getEmail());
			stmt.setString(18, clienteVo.getCondCompra());
			stmt.registerOutParameter(19, OracleTypes.CURSOR);
			
			stmt.execute();
			ResultSet rs = (ResultSet)stmt.getObject(19);
			while (rs.next()) {
				ClienteDTO documDTO = new ClienteDTO();
				documDTO.setIdCliente(rs.getString(1));
				listRetorno.add(documDTO);		    	
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  
			log.error("Error ClienteDAO.setCliente SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());
		} finally {			
			conn.close();
			conn = null;
		}
		return listRetorno;
	}
	

}
